<?php
include('rootlogin.php');
//include('../incsession.php');
session_start();


mysql_query("SET NAMES 'utf8'");
if($_POST['submit']=="back to index"){
	header("location: index.php");	
}
?>

<head>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<link rel="stylesheet" href="framestyle.css" type="text/css" media="screen">
<style type="text/css">

</style>
</head>


<body>

<div id="wrapper">
<div id="title">Online Listening Comprehension Program</div>

<div id="main">
<hr/>
<div id="title2">REGISTERED STUDENTS</div>
<div id="title3">Select a class and submit; the list of students and a download link will be displayed.</div>
<hr/>
<br><br>
<form method = POST action="student.php" >

<?php
echo '<select name="class" class="area">';

$query = "SELECT * FROM class ORDER BY year DESC, semester";
$result = mysql_query($query,$user_con);
while($row = mysql_fetch_array($result)){
	echo "<option value=".$row['classid'].">".$row['year']."&nbsp;/&nbsp;".$row['semester']."&nbsp;/&nbsp;".$row['teacher']."&nbsp;/&nbsp;".$row['name']."</option>";
}
echo '</select>';
echo '<input type="submit" name = "submit" value = "submit">';

if($_POST['submit']=="submit"){

	$query="SELECT name FROM class WHERE classid=".$_POST['class'];
	$result=mysql_query($query,$user_con);
	$row=mysql_fetch_array($result);

	echo '<br<br><a href="../student_data/student_'.$_POST['class'].'.xls">download the list</a><br><br>';
	echo "<table style='border-collapse:collapse'>";
	echo "<tr><td style='background-color: #C7DCFF'>list of registered students:";
	
	// Export xls
	/** Error reporting */ 
	error_reporting(E_ALL); 	
	/** PHPExcel */ 
	require_once "../Classes\PHPExcel.php"; 	
	/** PHPExcel_IOFactory */ 
	require_once "../Classes\PHPExcel\IOFactory.php";
	//require_once "../Classes\PHPExcel\Cell.php";
	$objPHPExcel = new PHPExcel(); 
	$objPHPExcel->setActiveSheetIndex(0);

	
	$filename = "../student_data/student_".$_POST['class'].".xls";

	$excel_row=1;
	
	$objPHPExcel->getActiveSheet()->getStyle(PHPExcel_Cell::stringFromColumnIndex(0).($excel_row))->getFont()->setSize(16);
	$objPHPExcel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(0).($excel_row), $row['name']);
	

	
	$lvl[1]="EASY";
	$lvl[2]="INTERMEDIATE";
	$lvl[3]="DIFFICULT";
	$query1 = "SELECT topicid, name FROM topic";
	$search1 = mysql_query($query1);
	$i=0;
	
	while($topic[$i] = mysql_fetch_array($search1)){
		$level=1;
		while($level<=3){
			//echo $topic[$i]['name']."<br>";
			$objPHPExcel->getActiveSheet()->getStyle(PHPExcel_Cell::stringFromColumnIndex($i*3+$level).($excel_row))->getFont()->setSize(16);
			$objPHPExcel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($i*3+$level).($excel_row), $topic[$i]['name']."-".$level);
			$level++;
		}
		$i++;
	}
	$excel_row++;
	$topic_num=$i;
	//echo $topic_num;
	
	$query2 = "SELECT uid FROM susers WHERE root=0 AND uid IN (SELECT uid FROM student_class WHERE classid=".$_POST['class'].")";
	$search2 = mysql_query($query2);
	while($student = mysql_fetch_array($search2)){
		$query3="SELECT user FROM susers WHERE uid=".$student['uid'];
		$search3 = mysql_query($query3);
		$user=mysql_fetch_array($search3);
		echo "<tr><td style='background-color: #EFF5FF'>".$user['user'];
		$objPHPExcel->getActiveSheet()->getStyle(PHPExcel_Cell::stringFromColumnIndex(0).($excel_row))->getFont()->setSize(16);
		$objPHPExcel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(0).($excel_row), $user['user']);
		for($i=0; $i<$topic_num; $i++){
			$level=1;
			while($level<=3){
				$query3="SELECT score FROM score WHERE uid=".$student['uid']." AND topicid=".$topic[$i]['topicid']." AND level=".$level;
				$search3 = mysql_query($query3);
				$score_num = mysql_num_rows($search3);	
				if($score_num==0)
					$score['score']='--';
				else
					$score = mysql_fetch_array($search3);
				//echo $topic[$i]['name']."<br>";
				$objPHPExcel->getActiveSheet()->getStyle(PHPExcel_Cell::stringFromColumnIndex($i*3+$level).($excel_row))->getFont()->setSize(16);
				$objPHPExcel->getActiveSheet()->getStyle(PHPExcel_Cell::stringFromColumnIndex($i*3+$level).($excel_row))->applyFromArray( 
							array("alignment"     => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT) ) );
				$objPHPExcel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($i*3+$level).($excel_row), (string)$score['score']);
				$level++;
			}
		}
		$excel_row++;
	}

	for($i=0; $i<$topic_num*3+1; $i++)
		$objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setAutoSize(true);
	//$objPHPExcel->getActiveSheet()->getColumnDimension("B")->setAutoSize(true);
	//$objPHPExcel->getActiveSheet()->getColumnDimension("C")->setAutoSize(true);
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel5"); 
	$objWriter->save($filename);
	echo "</table>";
}

?>
<br>

<div class="btn_right"><input type="submit" name = "submit" value = "back to index"></div>
</form>
</div>
</div>
</body>

